package com.loongtech.bi.advertisment.service.impl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.loongtech.bi.dao.CommonDao;
import com.loongtech.bi.advertisment.model.AdvOverviewData;
import com.loongtech.bi.advertisment.model.chart.ActiveChartData;
import com.loongtech.bi.advertisment.model.chart.ActiveNewRateData;
import com.loongtech.bi.advertisment.model.chart.ClickActiveRateData;
import com.loongtech.bi.advertisment.model.chart.ClickChartData;
import com.loongtech.bi.advertisment.model.chart.DiscountData;
import com.loongtech.bi.advertisment.model.chart.LtvData;
import com.loongtech.bi.advertisment.model.chart.NewAccountChartData;
import com.loongtech.bi.advertisment.model.chart.PayChartData;
import com.loongtech.bi.advertisment.model.chart.RemainData;
import com.loongtech.bi.advertisment.model.chart.TopActiveData;
import com.loongtech.bi.advertisment.model.chart.TopClickData;
import com.loongtech.bi.advertisment.model.chart.TopPayData;
import com.loongtech.bi.advertisment.service.AdvOverviewService;

@Service("advOverviewService")
public class AdvOverviewServiceImpl implements AdvOverviewService {

	@Resource(name = "commonDao")
	private CommonDao commonDao;

	@Override
	public AdvOverviewData getActiveTranDatas(String startday, String endday, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {

		StringBuilder sql = new StringBuilder();
		sql.append("select actEquipment,newEquipment, ");
		sql.append("if(actEquipment = 0 , 0 , round(newEquipment/actEquipment,4))newEquipmentRate, ");
		sql.append("newAccount,newPay, ");
		sql.append("if(newAccount = 0 , 0 , round(newPayUser/newAccount,4))payRate, ");
		sql.append("if(newAccount = 0 , 0 , round(discount/newAccount,2))cpa,discount,0 roi ");
		sql.append("from (  ");
		sql.append("select  sum(ifnull(tdactivate,0)) actEquipment, ");
		sql.append("sum(ifnull(newmac,0)) newEquipment, sum(ifnull(newuser,0)) newAccount,  ");
		sql.append("sum(ifnull(paymoney,0)) newPay, sum(ifnull(payuser,0)) newPayUser  ");
		sql.append("from td_adv_active a  ");
		sql.append("left join bi_advconfig bi on a.url = bi.id ");
		sql.append("where  a.logdate >=? and a.logdate <=? ");
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		sql.append(")a ");
		sql.append("left join ( ");
		sql.append("select round(sum(ifnull(discount,0)))discount from bi_advcost   ");
		sql.append("where ldate >=? and ldate <=?   ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append(")b on 1=1 ");
		sql.append("left join( ");
		sql.append("select  ifnull(sum(totalpay),0) totalpay      ");
		sql.append("from td_adv_totalpay a  left join bi_advconfig bi on a.url = bi.id    ");
		sql.append("where a.createdate >=? and a.createdate <=? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append(")c on 1=1 ");
		AdvOverviewData re = commonDao.queryObj(sql.toString(), new Object[] { startday, endday, startday, endday,
				startday, endday }, AdvOverviewData.class);
		int allRoi = getAllRoi(startday, endday, selectedChannelName, selectedAgent, selectedPlatform, selectedPaytype);
		re.setRoi(re.getDiscount() != 0 ? (double) allRoi / re.getDiscount() : 0);
		return re;
	}

	public int getAllRoi(String startday, String endday, String[] selectedChannelName, String[] selectedAgent,
			String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(sum(round(totalpay*   ");
		sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi   ");
		sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi   ");
		sql.append("else bi.roi end,2)),0)  roi from (   ");
		sql.append("select ifnull(a.createdate,'')  as dateStr,   ");
		sql.append("ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent ,ifnull(bi.platform,a.url) platform , ifnull(sum(totalpay),0) totalpay   ");
		sql.append("from td_adv_totalpay a   ");
		sql.append("left join bi_advconfig bi on a.url = bi.id     ");
		sql.append("where a.createdate >=? and a.createdate <=?   ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append(" group by a.createdate,bi.channelName,bi.agent,bi.platform)a    ");
		sql.append(" left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channelName = bi.channelName  and a.dateStr = bi.ldate  ");
		sql.append(" left join bi_advdefault def on 1=1  ");
		return commonDao.queryForInt(sql.toString(), new Object[] { startday, endday });
	}

	private String getParams(String[] param) throws Exception {
		StringBuilder s = new StringBuilder();
		for (String str : param) {
			s.append("'" + str + "',");
		}
		return s.substring(0, s.length() - 1);
	}

	@Override
	public List<ClickChartData> getClickChartUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(a.logdate,'') rangeStr,sum(tdclick) clickNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <= ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate order by a.logdate desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, ClickChartData.class);
	}

	@Override
	public List<ActiveChartData> getAcitveChartUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(a.logdate,'') rangeStr,sum(tdactivate) activeNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <= ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate order by a.logdate desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, ActiveChartData.class);
	}

	@Override
	public List<NewAccountChartData> getNewAccountChartUIData(String startDay, String endDay,
			String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype)
			throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(a.logdate,'') rangeStr,sum(newmac) newAccountNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <= ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate order by a.logdate desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, NewAccountChartData.class);
	}

	@Override
	public List<PayChartData> getPayChartUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(a.logdate,'') rangeStr,sum(paymoney) payNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <= ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate order by a.logdate desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, PayChartData.class);
	}

	@Override
	public List<ClickActiveRateData> getClickActiveRateUIData(String startDay, String endDay,
			String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype)
			throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(a.logdate,'') rangeStr, ");
		sql.append("if(sum(tdclick) =0 || sum(tdclick) is null, 0, sum(tdactivate)/sum(tdclick)) activeRate ");
		sql.append("from td_adv_active a  ");
		sql.append("left join bi_advconfig bi on a.url = bi.id ");
		sql.append("where a.logdate >= ? and a.logdate <= ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate order by a.logdate desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, ClickActiveRateData.class);
	}

	@Override
	public List<ActiveNewRateData> getActiveNewRateUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ifnull(a.logdate,'') rangeStr, ");
		sql.append("if(sum(tdactivate) =0 || sum(tdactivate) is null, 0, sum(newmac)/sum(tdactivate)) newRate ");
		sql.append("from td_adv_active a  ");
		sql.append("left join bi_advconfig bi on a.url = bi.id ");
		sql.append("where a.logdate >= ? and a.logdate <= ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate order by a.logdate desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, ActiveNewRateData.class);
	}

	@Override
	public List<DiscountData> getDiscountUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select rangeStr , round(discount/newuser,2)cpa from(  ");
		sql.append("select ifnull(a.logdate,'') rangeStr,sum(newuser) newuser from td_adv_active a   ");
		sql.append("left join bi_advconfig bi on a.url = bi.id   ");
		sql.append("where a.logdate >= ? and a.logdate <=  ?  ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate)a  ");
		sql.append("left join (  ");
		sql.append("select ldate,round(sum(discount))discount from bi_advcost a  ");
		sql.append("where a.ldate >= ? and a.ldate <=  ?  ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and a.channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and a.agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and a.platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by a.ldate  ");
		sql.append(")b on a.rangeStr = b.ldate  ");
		sql.append("order by rangeStr desc  ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay, startDay, endDay },
				DiscountData.class);
	}

	@Override
	public List<RemainData> getRemainUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select a.rangeStr,  ");
		sql.append("max(case when b.logdate = date_add(STR_TO_DATE(a.rangeStr,'%Y-%m-%d'),interval 1 day) and a.newuser !=0 then round(b.remainnum/a.newuser,3) else 0 end)the2left , ");
		sql.append("max(case when b.logdate = date_add(STR_TO_DATE(a.rangeStr,'%Y-%m-%d'),interval 2 day) and a.newuser !=0 then round(b.remainnum/a.newuser,3) else 0 end)the3left , ");
		sql.append("max(case when b.logdate = date_add(STR_TO_DATE(a.rangeStr,'%Y-%m-%d'),interval 6 day) and a.newuser !=0 then round(b.remainnum/a.newuser,3) else 0 end)the7left from ( ");
		sql.append("select ifnull(a.logdate,'') rangeStr,sum(newuser) newuser from td_adv_active a  ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <=  ? ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("group by a.logdate)a ");
		sql.append("left join ( ");
		sql.append("select a.createdate,a.logdate,sum(remainnum)remainnum from td_adv_newremain a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.createdate >= ? and a.createdate <=  ?  ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("and (a.logdate = date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval 1 day)  ");
		sql.append("or a.logdate = date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval 2 day)  ");
		sql.append("or a.logdate = date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval 6 day)) ");
		sql.append("group by a.createdate,a.logdate)b on a.rangeStr = b.createdate ");
		sql.append("group by a.rangeStr ");
		sql.append("order by a.rangeStr desc  ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay, startDay, endDay },
				RemainData.class);
	}

	@Override
	public List<LtvData> getLtvUIData(String startDay, String endDay, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select a.rangeStr , the2ltv , (the2ltv + the3ltv)the3ltv , (the2ltv + the3ltv + the7ltv)the7ltv from ( ");
		sql.append("select ifnull(b.createdate,'') rangeStr,    ");
		sql.append("max(case when b.logdate = date_add(STR_TO_DATE(b.createdate,'%Y-%m-%d'),interval 0 day) then b.totalpay else 0 end)the2ltv ,   ");
		sql.append("max(case when b.logdate = date_add(STR_TO_DATE(b.createdate,'%Y-%m-%d'),interval 2 day) then b.totalpay else 0 end)the3ltv ,   ");
		sql.append("max(case when b.logdate = date_add(STR_TO_DATE(b.createdate,'%Y-%m-%d'),interval 6 day) then b.totalpay else 0 end)the7ltv from (   ");
		sql.append("select a.createdate,a.logdate,sum(totalpay)totalpay from td_adv_totalpay a   ");
		sql.append("left join bi_advconfig bi on a.url = bi.id    ");
		sql.append("where a.createdate >= ? and a.createdate <=  ?  ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		sql.append("and (a.logdate = date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval 0 day)     ");
		sql.append("or a.logdate = date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval 2 day)     ");
		sql.append("or a.logdate = date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval 6 day))    ");
		sql.append("group by a.createdate,a.logdate)b    ");
		sql.append("group by b.createdate)a    ");
		sql.append("order by rangeStr desc   ");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay},
				LtvData.class);
	}

	@Override
	public List<TopClickData> getTopClickUIData(String startDay, String endDay) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from (");
		sql.append("select ifnull(bi.channelName,'') rangeStr,sum(tdclick) clickNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <=  ? ");
		sql.append("group by bi.channelName ");
		sql.append("order by clickNumber desc limit 10 ");
		sql.append(")b order by clickNumber");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, TopClickData.class);
	}

	@Override
	public List<TopActiveData> getTopActiveUIData(String startDay, String endDay) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from (");
		sql.append("select ifnull(bi.channelName,'') rangeStr,sum(tdactivate) activeNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <=  ? ");
		sql.append("group by bi.channelName ");
		sql.append("order by activeNumber desc limit 10 ");
		sql.append(")b order by activeNumber");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, TopActiveData.class);
	}

	@Override
	public List<TopPayData> getTopPayUIData(String startDay, String endDay) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from (");
		sql.append("select ifnull(bi.channelName,'') rangeStr,sum(tdclick) payNumber from td_adv_active a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		sql.append("where a.logdate >= ? and a.logdate <=  ? ");
		sql.append("group by bi.channelName ");
		sql.append("order by payNumber desc limit 10 ");
		sql.append(")b order by payNumber");
		return commonDao.queryObjList(sql.toString(), new Object[] { startDay, endDay }, TopPayData.class);
	}
}
